package DAL.JDBC;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import DAL.Entites.*;


public class GameJDBC {
	
	Connection conn;

	String tableName = "games"; 
	String basicSQL = String.format("SELECT %1$s.*, genres.GenreName, consoletypes.ConsoleName FROM %1$s, genres, consoletypes where %1$s.GenreId = genres.Id and %1$s.consoleId = consoletypes.Id", tableName);
	
	public GameJDBC(Connection connection) {
		this.conn = connection;
	}
	
	public int insert(String Name, String Description, String Trailer ,int ConsoleId, int GenreId, int Quantity, int Price) {
		// this is for selecting db incase we do insert first
		// ((DriverManagerDataSource)dataSource).setUrl("jdbc:mysql://localhost:3306/TestData");
		int insertedId = 0;
		String SQL = String.format("insert into %s values(null,'%s','%s','%s','%s','%s','%s',%s)",
				tableName, Name, Description, Trailer, ConsoleId, GenreId, Quantity, Price);
		
		try {
			Statement st = this.conn.createStatement();
			st.executeUpdate(SQL, Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = st.getGeneratedKeys();
			if (rs != null && rs.next()) {
				insertedId = rs.getInt(1);
			}
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		return insertedId;
	}
	
	private Game mapRow(ResultSet rs) throws SQLException {
		Game game = new Game(
				rs.getInt("Id"),
				rs.getString("Name"),
				rs.getString("Description"),
				rs.getString("TrailerURL"),
				rs.getInt("ConsoleId"),
				rs.getInt("GenreId"),
				rs.getInt("Quantity"),
				rs.getFloat("Price"),
				null,
				rs.getString("ConsoleName"),
				rs.getString("GenreName")
				);

		System.out.println("mapRow");

		return game;
	}
	
	public List<Game> listGames() {
		List<Game> gamesList = new ArrayList<Game>();
		String SQL = basicSQL;
		try {
			Statement st = conn.createStatement();
			for (ResultSet rs = st.executeQuery(SQL); rs.next();) {
				gamesList.add(mapRow(rs));
			}
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

		return gamesList;
	}
	
	public List<Game> listGamesByCriteria(int ConsoleId, int GenreId) {
		List<Game> gamesList = new ArrayList<Game>();
		String SQL = basicSQL;
		if(ConsoleId == 0 && GenreId != 0)
		{
			SQL += String.format(" and %s.GenreId = %s", tableName, GenreId);
		}
		else if(ConsoleId != 0 && GenreId == 0)
		{		
			SQL += String.format(" and %s.ConsoleId = %s", tableName,ConsoleId);
		}
		else if(ConsoleId != 0 && GenreId != 0)
		{	 
			SQL += String.format(" and %s.ConsoleId = %s and %s.GenreId = %s",tableName, ConsoleId, tableName, GenreId);
		}
		try {
			Statement st = conn.createStatement();
			for (ResultSet rs = st.executeQuery(SQL); rs.next();) {
				gamesList.add(mapRow(rs));
			}
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

		return gamesList;
	}
	
	@SuppressWarnings("finally")
	public boolean updateGame(Game game)
	{
		boolean updated = false;
		String Query = String.format(
				"update %s set Name = '%s', Description = '%s', TrailerURL = '%s', ConsoleId = %s, GenreId = %s, Quantity = %s, Price = %s  where id = %s",
				tableName,
				game.getName(),
				game.getDescription(),
				game.getTrailer(),
				game.getConsoleId(),
				game.getGenreId(),
				game.getQuantity(),
				game.getPrice(),
				game.getId()
		);
		try {
			Statement st = conn.createStatement();
			st.execute(Query);
			updated = true;
			st.close();
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		}
		finally{
			return updated;
		}
		
	}
	
	@SuppressWarnings("finally")
	public Game getGameById(int id) {
		Game game = null;
		String SQL = basicSQL;
		SQL += String.format(" and %s.id = %s", tableName, id);
		try {
			Statement st = conn.createStatement();
			for (ResultSet rs = st.executeQuery(SQL); rs.next();) {
				game = mapRow(rs);
			}
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		finally{
			return game;
		}
	}
	
	public boolean removeGame(int id) {
		boolean result = false;
		String SQL = String.format("delete from %s where id = %s", tableName, id);
		try {
			Statement st = conn.createStatement();
			st.execute(SQL);
			result = true;
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
			result = false;
		}
		
		return result;
	}
	
}
